package com.lzxuni.controller;

import com.lzxuni.po.QxData;
import com.lzxuni.utils.MethodUtil;
import com.lzxuni.utils.UuidUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelController {
    public  List<String> excel(String filePath) throws IOException {
        System.out.println("filepath的地址是"+filePath);
        File excelFile = null;// Excel文件对象
        InputStream is = null;// 输入流对象
        String cellStr = null;// 单元格，最终按字符串处理
        String data =null;
        List<String> listdata = new ArrayList<String>();
        try {
            excelFile = new File(filePath);
            is = new FileInputStream(excelFile);// 获取文件输入流
            XSSFWorkbook workbook2007 = new XSSFWorkbook(is);// 创建Excel2007文件对象
            XSSFSheet sheet = workbook2007.getSheetAt(0);// 取出第一个工作表，索引是0
            // 开始循环遍历行，表头不处理，从1开始
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                //  for (int i = 0; i <= 490; i++) {
                QxData qxData = new QxData();
                qxData.setId(UuidUtil.get32UUID());
                XSSFRow row = sheet.getRow(i);// 获取行对象
                if (row == null) {// 如果为空，不处理
                    continue;
                }
                // 循环遍历单元格
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    // for (int j = 0; j < 2; j++) {
                    XSSFCell cell = row.getCell(j);// 获取单元格对象
                    if (cell == null) {// 单元格为空设置cellStr为空串
                        cellStr = "";
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 对布尔值的处理
                        cellStr = String.valueOf(cell.getBooleanCellValue());
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 对数字值的处理
                        cellStr = cell.getNumericCellValue() + "";
                    } else {// 其余按照字符串处理
                        cellStr = cell.getStringCellValue();
                    }
                    // 下面按照数据出现位置封装到bean中
                    if (j == 0) {
                       data=cellStr;
                    } else if (j == 1) {
                        data+=","+StringUtils.substringBefore(cellStr,".");
                    }else if(j == 2){
                        data+=","+StringUtils.substringBefore(cellStr,".");
                    }else if(j==3){
                        data+=","+StringUtils.substringBefore(cellStr,".");
                    }else if(j==4){
                        data+=","+StringUtils.substringBefore(cellStr,".");
                    }else if(j==5){
                        data+=","+StringUtils.substringBefore(cellStr,".");
                    }else if(j==6){
                        data+=","+StringUtils.substringBefore(cellStr,".");

                    }else if(j==7){
                        data+=","+StringUtils.substringBefore(cellStr,".");
                    }else if(j==8){
                        data+=","+StringUtils.substringBefore(cellStr,".");
                    }else if(j==9){
                        data+=","+StringUtils.substringBefore(cellStr,".");
                        System.out.println("data的数据是"+data);
                        listdata.add(data);
                    }

                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        }  finally {// 关闭文件流
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        System.out.println("list的长度是"+listdata.size());
        return listdata;

    }

    public static void main(String[] args) throws IOException, ParseException {
      /*  ExcelController amm = new ExcelController();
        amm.excel("C:\\Users\\zth-1\\Downloads\\asd\\17042657003.xls");*/
        String a="19181010010203 ";
        a=a.replace(" ","");
        a=a.replace(":","");
        a=a.replace("-","");
        SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
        Date date0 = sdf.parse(a);
        String str = sdf.format(date0);
        str=str.substring(2);
        String createTime=MethodUtil.formatDate(0,sdf.parse(a));
        System.out.println(date0);
        System.out.println(createTime);
        System.out.println(str);
    }
}
